#    Copyright 2012 Tri-County Electric Cooperative, St. Matthews, South Carolina
#    This file is part of COOPIP.
#    COOPIP is free software: you can redistribute it and/or modify
#    it under the terms of the GNU General Public License as published by
#    the Free Software Foundation, either version 3 of the License, or
#    (at your option) any later version.
#    COOPIP is distributed in the hope that it will be useful,
#    but WITHOUT ANY WARRANTY; without even the implied warranty of
#    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#    GNU General Public License for more details.
#    You should have received a copy of the GNU General Public License
#    along with COOPIP.  If not, see <http://www.gnu.org/licenses/>.

import sys
NeededPaths=['/opt/coopip/djangoproject/include', '/opt/coopip/djangoproject', '/opt/cis', '/opt/oms/', '/opt/amr', '/opt/gis','/opt/gos', '/opt']
for path in NeededPaths:
    if(not path in sys.path):
        sys.path.append(path)
import httplib
import os, csv, shutil, datetime, time, reportlab, djangoproject.settings, datetime
from django.contrib.gis.db import models
from django.contrib.gis.geos import *
from django.http import HttpResponse
from django.contrib.gis import admin
import _mysql as mysql
from django.db import transaction
import django, urllib2, urllib
import COOPIP_Form
import CIS, AMR, GIS, GOS  #Comment this out before doing a syncdb
from ftplib import FTP
import subprocess
from bs4 import BeautifulSoup
import re
import cPickle
import smtplib
from email.mime.text import MIMEText
import subprocess


 


class AlarmStatus(models.Model):
    """Lookup Table of possible alarm statuses"""
    using='default'
    Name=models.TextField(db_column='name', unique=True)
    objects=models.GeoManager()
    class Meta:
        db_table=u'alarmstatus'
        app_label='djangoproject'
    def __unicode__(self):
        return str(self.Name)
    @staticmethod
    def CreateInitial():
        Statuses=['Away Armed', 'Ready', 'Exit/Entry Delay', 'Busy', 'Not Ready', 'In Alarm',
                  'UNKNOWN Cannot Read File', 'UNKNOWN Device h1', 'UNKNOWN System Time']
        for i in Statuses:
            if(not AlarmStatus.objects.filter(Name=i)):
                New=AlarmStatus()
                New.Name=i
                New.save()

class AlarmHistory(models.Model):
    using = 'default'
    NetworkDevice=models.ForeignKey("NetworkDevice", db_column='networkdevice_id')
    AlarmStatus=models.ForeignKey('AlarmStatus', db_column='alarmstatus_id') #Status before performing the command
    ZoneStatus=models.TextField(db_column='zonestatus')  #pickled dict['zoneid']=Status, Closed is assumed if not in dict
    CommandDescription=models.TextField(db_column='commanddescription', null=True, blank=True)
    CommandUsername=models.TextField(db_column='commandusername', null=True, blank=True)
    SystemDateTime=models.DateTimeField(db_column='systemdatetime', null=True, blank=True) #At Creation
    LastUpdateDateTime=models.DateTimeField(db_column='lastupdatedatetime', null=True, blank=True)
    CreateDateTime=models.DateTimeField(db_column='createdatetime', null=True, blank=True)
    objects=models.GeoManager()
    class Meta:
        db_table=u'alarmstatushistory'
        app_label='djangoproject'
    def GetZoneStatusDict(self):
        try:
            return cPickle.loads(str(self.ZoneStatus))
        except cPickle.UnpicklingError:
            return {}
    def SetZoneStatusDict(self, ZoneStatusDictString):
        self.ZoneStatus = cPickle.dumps(ZoneStatusDictString)
    @staticmethod
    def NewAlarmHistory(NetworkDevice, AlarmStatusFileName, CommandDescription='None', CommandUsername='COOPIP'):
        PotentialPreviousEntry=AlarmHistory.objects.filter(NetworkDevice=NetworkDevice).order_by('-CreateDateTime')
        if(PotentialPreviousEntry.count()):
            PotentialPreviousEntry = PotentialPreviousEntry[0]
        else:
            PotentialPreviousEntry = None
        alarmStatus, SystemDateTime, ZoneStatusDict = AlarmHistory.ParseAlarmFile(AlarmStatusFileName)
        if(PotentialPreviousEntry):
            if(PotentialPreviousEntry.CommandDescription == CommandDescription and PotentialPreviousEntry.CommandUsername == CommandUsername and
               PotentialPreviousEntry.GetZoneStatusDict() == ZoneStatusDict and PotentialPreviousEntry.AlarmStatus == AlarmStatus.objects.filter(Name=alarmStatus)[0] and
               (datetime.datetime.now() - PotentialPreviousEntry.CreateDateTime).seconds < (60*60)):
                PotentialPreviousEntry.LastUpdateDateTime=datetime.datetime.now()
                PotentialPreviousEntry.save()
                return
        New=AlarmHistory()
        New.NetworkDevice=NetworkDevice
        New.AlarmStatus=AlarmStatus.objects.filter(Name=alarmStatus)[0]
        New.SetZoneStatusDict(ZoneStatusDict)
        New.CommandDescription=CommandDescription
        New.CommandUsername=CommandUsername
        New.SystemDateTime=SystemDateTime
        New.CreateDateTime=datetime.datetime.now()
        New.LastUpdateDateTime=New.CreateDateTime
        New.save()
    @staticmethod
    def ParseAlarmFile(FileName):  
        print FileName
        try:
            File=open(FileName)
            FileString = File.read()
            File.close()
        except:
            print 'UNKNOWN Cannot Read File'
            return 'UNKNOWN Cannot Read File',None, {}

        #FileString = FileString.replace('Away Armed', 'In Alarm') #FIXME GROBY TESTINT ALARM SYSTEM

        soup = BeautifulSoup(FileString)
        if(not soup):
            return 'NO RESPONSE', None, {}

        if(not str(soup.h1.text).strip() == 'EnvisaLink 3'):
            print soup.h1.text
            print 'UNKNOWN Device h1'
            return 'UNKNOWN Device h1',None, {}
        if(not '- System Time' in str(soup.th.text)):
            print 'UNKNOWN System Time'
            return 'UNKNOWN System Time',None, {}
        ZoneStatusDict={}
        print soup.th.text[:4] + ' ' + soup.th.text[5:7] + ' ' + soup.th.text[8:10] + ' ' + soup.th.text[11:13] + ' ' + soup.th.text[14:16]
        try:
            SystemDateTime=datetime.datetime(int(soup.th.text[:4]), int(soup.th.text[5:7]), int(soup.th.text[8:10]), int(soup.th.text[11:13]), int(soup.th.text[14:16]))
        except ValueError:
            SystemDateTime=None
        ZoneTable=soup.table.next_sibling.next_sibling
        for i in ZoneTable.find_all('span'):
            if(not 'title' in i.attrs):  #Zones all have a title attribute and are listed 1st in the file
                break
            if('CLOSED: ' in i.attrs['title'] and not 'Seconds Ago' in i.attrs['title']):
                continue #All zones are assumed closed, so no need to store in the database
            try:
                ZoneNumber=int(i.text) #Only store integer values
                ZoneStatus=' '.join(re.findall("[0-9a-zA-Z]+", i.attrs['title']))  #Only store characters of the alphabet
                print str(ZoneNumber) + '   ' + str(ZoneStatus)
            except:
                continue
            ZoneStatusDict[str(ZoneNumber)] = ZoneStatus
        SystemStatus=str(soup.table.next_sibling.next_sibling.tr.next_sibling.td.next_sibling.tr.td.next_sibling.text).strip()
        return SystemStatus, SystemDateTime, ZoneStatusDict


class NetworkDeviceModel(models.Model):
    using='default'
    Name=models.TextField(db_column='name', null=True, blank=True, db_index=True)
    #Add some indicators here to separate devices into various classes
    PrinterIndicator=models.NullBooleanField(db_column='printerindicator', null=True, blank=True)
    CameraIndicator=models.NullBooleanField(db_column='cameraindicator', null=True, blank=True)
    PhoneIndicator=models.NullBooleanField(db_column='phoneindicator', null=True, blank=True)
    RadioIndicator=models.NullBooleanField(db_column='radioindicator', null=True, blank=True)
    Description=models.TextField(db_column='description', null=True, blank=True)
    objects=models.GeoManager()
    class Meta:
        db_table=u'networkdevicename'
        app_label='djangoproject'
    @staticmethod
    def CreateInitial():
        DeviceNames=['ProST Radio', 'Moxa', 'SCPA-G2','NetBooter NP-02(B)', 'SensorProbe2', 'DSC 1832 Alarm',
                     'Kyocera CS 255', 'CP-7941G', 'CP-7906G', 'CP-7961G', 'HP LaserJet 4240n', 'Xerox Phaser 6128MFP-N',
                     'Cisco Catalyst 3560', 'Cisco 2821', 'CP-7942G', 'HP OfficeJet 6310', 'Kyocera CS 4550ci',
                     'Xerox Phaser 6128MFP-N', 'Moxa NP6150', 'Rocket M5 Radio']
        for i in DeviceNames:
            if(not NetworkDeviceModel.objects.filter(Name=i)):
                New=NetworkDeviceModel()
                New.Name=i
                New.save()
    def __unicode__(self):
        return str(self.Name)

class NetworkDevice(models.Model):
    using='default'
    Name=models.TextField(db_column='name', null=True, blank=True, db_index=True)
    Description=models.TextField(db_column='description', null=True, blank=True)
    IPAddress=models.TextField(db_column='ipaddress', null=True, blank=True)  #Current/Primary IP Address for a Network Device
    MACAddress=models.TextField(db_column='macaddress', null=True, blank=True)  #Current/Primary MAC Address for a Network Device
    SerialNumber=models.TextField(db_column='serialnumber', null=True, blank=True)
    NetworkDeviceModel=models.ForeignKey('NetworkDeviceModel', db_column='networkdevicemodel_id')
    AMRSubstationName=models.TextField(db_column='amrsubstationname', null=True, blank=True)
    ServiceTag=models.TextField(db_column='servicetag', null=True, blank=True)
    ServerIndicator=models.NullBooleanField(db_column='serverindicator', null=True, blank=True)
    ActiveIndicator=models.NullBooleanField(db_column='activeindicator', null=True, blank=True)
    PendingArmUsername=models.TextField(db_column='pendingarmusername', null=True, blank=True)
    PendingDisarmUsername=models.TextField(db_column='pendingdisarmusername', null=True, blank=True)
    objects=models.GeoManager()
    class Meta:
        db_table=u'networkdevice'
        app_label='djangoproject'
    def __unicode__(self):
        return str(self.Name)
    @staticmethod
    def ProcessPendingArmDisarmOperations():
        PendingArm=NetworkDevice.objects.filter(PendingArmUsername__isnull=False)
        for i in PendingArm:
            print "************************************** PENDING ARM *******************************************"
            i.AlarmArm(i.PendingArmUsername)
            i.PendingArmUsername=None
            i.save()
        PendingDisarm=NetworkDevice.objects.filter(PendingDisarmUsername__isnull=False)
        for i in PendingDisarm:
            print "************************************** PENDING DISARM *******************************************"

            i.AlarmDisarm(i.PendingDisarmUsername)
            i.PendingDisarmUsername=None
            i.save()

    def Test(self):
        if(not self.ActiveIndicator):
            return str(self) + " is not active"
        networkDeviceTest=NetworkDeviceTest()
        if(not self.NetworkDeviceModel):
            print "No NetworkDeviceModel for NetworkDevice id:  " + str(self.id)
            return 'No NetworkDeviceModel for NetworkDevice id: ' + str(self.NetworkDeviceModel)
        elif(self.NetworkDeviceModel.Name == 'ProST Radio'):
            try:
                TestURL=self.GetTestURL()
                networkDeviceTest.ResponseIndicator=True
                networkDeviceTest.BaseStationIdentifier=self.ExtractField(TestURL, 'BS Identifier')
                networkDeviceTest.DownlinkFrequency=self.ExtractField(TestURL, 'Downlink Frequency', ['MHz'], True)
                networkDeviceTest.DownlinkChannelBandwidth=self.ExtractField(TestURL, 'Downlink Channel Bandwidth', ['MHz'], True)
                networkDeviceTest.SignalToNoiseRatio=self.ExtractField(TestURL, 'SNR', ['dB'], True)
                networkDeviceTest.EffectiveSignalToNoiseRatio=self.ExtractField(TestURL, 'Effective SNR', ['dB'], True)
                networkDeviceTest.ReceivedSignalStrength=self.ExtractField(TestURL, 'Received Signal Strength', ['dBm'], True)
                networkDeviceTest.DownlinkModulation=self.ExtractField(TestURL, 'Modulation', ['MHz'], True)
                networkDeviceTest.UplinkFrequency=self.ExtractField(TestURL, 'Uplink Frequency', ['MHz'], True)
                networkDeviceTest.UplinkChannelBandwidth=self.ExtractField(TestURL, 'Uplink Channel Bandwidth', ['MHz'], True)
                networkDeviceTest.TransmitPower=self.ExtractField(TestURL, 'Tx Power', ['dBm'], True)
                networkDeviceTest.UplinkModulation=self.ExtractField(TestURL[TestURL.find('Tx Power'):], 'Modulation')
            except:
                TestURL=''
                networkDeviceTest.ResponseIndicator=False
        elif(self.NetworkDeviceModel.Name in ['Rocket M5 Radio', 'Rocket M5 GPS Radio', 'Ubiquiti ToughSwitch']):
            try:
                StartTime=datetime.datetime.now()
                TestURL=self.GetTestURL()
                networkDeviceTest.ResponseIndicator=True
                EndTime=datetime.datetime.now()
                networkDeviceTest.PingTime = (EndTime - StartTime).total_seconds()
            except:
                TestURL=''
                networkDeviceTest.ResponseIndicator=False
        elif(self.NetworkDeviceModel.Name in ['Moxa', 'SCPA-G2','NetBooter NP-02(B)']):
            SuccessIndicator, PingTime = self.Ping()
            networkDeviceTest.ResponseIndicator=SuccessIndicator
            networkDeviceTest.PingTime = PingTime
            #print str(self.IPAddress).ljust(25) + str(self.Name).ljust(25) + str(SuccessIndicator).ljust(25) + str(PingTime)
        elif(self.NetworkDeviceModel.Name == 'SensorProbe2'):
            try:
                TestURL=self.GetTestURL()
                StartKey='sr[0]='
                Start=TestURL.find(StartKey)+len(StartKey)
                Stop=TestURL.find('var ss=new Array;')
                ParsableString=TestURL[Start:Stop].replace('\'','').replace(' %<br>',';').replace(' &deg;F','').replace('sr[1]=','').replace(' Volts;','')
                ParsedStrings=ParsableString.split(';')
                networkDeviceTest.ResponseIndicator=True
                networkDeviceTest.Voltage=float(ParsedStrings[2])
                networkDeviceTest.Temperature=float(ParsedStrings[1])
                networkDeviceTest.RelativeHumidity=float(ParsedStrings[0])
            except:
                TestURL=''
                networkDeviceTest.ResponseIndicator=False
        else:
            return 'Device Not Supported:  ' + str(self.NetworkDeviceModel)
        networkDeviceTest.NetworkDevice=self
        networkDeviceTest.DateTime=datetime.datetime.now()
        networkDeviceTest.save()
    def GetTestURL(self):
        if(self.NetworkDeviceModel.Name == 'ProST Radio'):
            File = urllib.urlopen('http://' + self.IPAddress + '/sig_params/')  #DO NOT USE URLLIB2 With the ProST Radios, they do not work well (at all?) with it.  GROBY 11/29/2012
            try:
                String = File.read()
            except:
                return ''
            File.close()
            return String
        elif(self.NetworkDeviceModel.Name == 'SensorProbe2'):
            File= urllib2.urlopen('http://' + self.IPAddress, timeout = 20)
            try:
                String = File.read()
            except:
                return ''
            File.close()
            return String
        elif(self.NetworkDeviceModel.Name in ['Rocket M5 Radio', 'Rocket M5 GPS Radio', 'Ubiquiti ToughSwitch']):
            File= urllib2.urlopen('http://' + self.IPAddress + '/login.cgi', timeout = 3)  #FIXME, need to login with a read only account to get signal strength?
            try:
                String = File.read()
            except:
                return ''
            File.close()
            return String
        else:
            return ''
    def Ping(self, IPAddress=None):
        import subprocess, shlex
        if(not IPAddress):
            IPAddress=str(self.IPAddress)
        try:
            commandOutput=subprocess.check_output(['ping', '-c', '1', str(IPAddress)])
            start=commandOutput.index('time=') + 5
            end=commandOutput[start:].index('ms')
            pingAsFloat=float(commandOutput[start:(start+end)])
            SuccessIndicator=True
            time=pingAsFloat
        except:
            SuccessIndicator=False
            time=None
        return [SuccessIndicator, time]

    def ExtractField(self, TestURL, Field, ReplaceFields=[], FloatIndicator=False):
        Temp = TestURL[TestURL.find(Field) + len(Field):]
        Temp = Temp[:Temp.find('</tr>')]
        Temp=Temp.replace('</b>', '').replace('</td>', '').replace('<td>', '')
        for field in ReplaceFields:
            Temp=Temp.replace(field, '')
        if(FloatIndicator):
            try:
                Temp=float(Temp)
            except:
                Temp=None
        return Temp
    def GetRecentStatus(self):
        import datetime
        StartDate=datetime.datetime.now() - datetime.timedelta(days=1)
        if(self.NetworkDeviceModel.Name == 'ProST Radio'):
            RecentDateTime=datetime.datetime.now() - datetime.timedelta(hours=1)
            FailedTests=NetworkDeviceTest.objects.filter(NetworkDevice=self).filter(DateTime__gte=StartDate).filter(EffectiveSignalToNoiseRatio=None).count()
            TotalTests=NetworkDeviceTest.objects.filter(NetworkDevice=self).filter(DateTime__gte=StartDate).count()
            LatestAttempt=NetworkDeviceTest.objects.filter(NetworkDevice=self).filter(DateTime__gte=StartDate).order_by('-DateTime')[0]
            Percent=int(100 * (TotalTests - FailedTests) / float(TotalTests))
            if(LatestAttempt.EffectiveSignalToNoiseRatio and LatestAttempt.DateTime > RecentDateTime):
                Status='Online'
            elif(not LatestAttempt.EffectiveSignalToNoiseRatio and LatestAttempt.DateTime > RecentDateTime):
                Status='Offline'
            else:
                Status='Unknown'
            return Status + ' ' + str(Percent) + '%', Percent
        if(self.NetworkDeviceModel.Name in ['Moxa', 'SCPA-G2','NetBooter NP-02(B)', 'Rocket M5 Radio', 'Rocket M5 GPS Radio', 'Ubiquiti ToughSwitch']):
            RecentDateTime=datetime.datetime.now() - datetime.timedelta(hours=1)
            FailedTests=NetworkDeviceTest.objects.filter(NetworkDevice=self).filter(DateTime__gte=StartDate).exclude(ResponseIndicator=True).count()
            TotalTests=NetworkDeviceTest.objects.filter(NetworkDevice=self).filter(DateTime__gte=StartDate).count()
            LatestAttempt=NetworkDeviceTest.objects.filter(NetworkDevice=self).filter(DateTime__gte=StartDate).order_by('-DateTime')[0]
            Percent=int(100 * (TotalTests - FailedTests) / float(TotalTests))
            if(LatestAttempt.ResponseIndicator and LatestAttempt.DateTime > RecentDateTime):
                Status='Online'
            elif(not LatestAttempt.ResponseIndicator and LatestAttempt.DateTime > RecentDateTime):
                Status='Offline'
            else:
                Status='Unknown'
            return Status + ' ' + str(Percent) + '%', Percent
        if(self.NetworkDeviceModel.Name == 'SensorProbe2'):
            RecentDateTime=datetime.datetime.now() - datetime.timedelta(minutes=30)
            FailedTests=NetworkDeviceTest.objects.filter(NetworkDevice=self).filter(DateTime__gte=StartDate).exclude(ResponseIndicator=True).count()
            TotalTests=NetworkDeviceTest.objects.filter(NetworkDevice=self).filter(DateTime__gte=StartDate).count()
            LatestAttempt=NetworkDeviceTest.objects.filter(NetworkDevice=self).filter(DateTime__gte=StartDate).order_by('-DateTime')[0]
            Percent=int(100 * (TotalTests - FailedTests) / float(TotalTests))
            if(LatestAttempt.ResponseIndicator and LatestAttempt.DateTime > RecentDateTime):
                Status='Online'
            elif(not LatestAttempt.ResponseIndicator and LatestAttempt.DateTime > RecentDateTime):
                Status='Offline'
            else:
                Status='Unknown'
            Voltage=LatestAttempt.Voltage
            Temperature=LatestAttempt.Temperature
            Humidity=LatestAttempt.RelativeHumidity
            return Status + ' ' + str(Percent) + '%', Voltage, Temperature, Humidity
        if(self.NetworkDeviceModel.Name == 'DSC 1832 Alarm'):
            alarmHistory = AlarmHistory.objects.filter(NetworkDevice=self).order_by('-LastUpdateDateTime')
            if(not alarmHistory.count()):
                return 'Unknown', 'N/A'
            return alarmHistory[0].AlarmStatus.Name, str(alarmHistory[0].LastUpdateDateTime)[5:16]
            
        return 'Not Implemented for this device', None
    def AlarmArm(self, Username):
        try:
            os.remove(djangoproject.settings.AlarmArmPath + str(self.IPAddress))
        except:
            pass
        if(os.path.exists(djangoproject.settings.AlarmArmPath + str(self.IPAddress))):
            return 
        print 'wget --user=' + djangoproject.settings.AlarmUsername + ' --password=' + djangoproject.settings.AlarmPassword + \
                   ' http://' + str(self.IPAddress) + r'/2?A=3\&p=1\&X=7103 -O ' + djangoproject.settings.AlarmArmPath + str(self.IPAddress)

        os.system('wget --user=' + djangoproject.settings.AlarmUsername + ' --password=' + djangoproject.settings.AlarmPassword + \
                   ' http://' + str(self.IPAddress) + r'/2?A=3\&p=1\&X=7103 -O ' + djangoproject.settings.AlarmArmPath + str(self.IPAddress))
        AlarmHistory.NewAlarmHistory(NetworkDevice=self, AlarmStatusFileName=djangoproject.settings.AlarmDisarmPath + str(self.IPAddress), 
                                     CommandDescription='Arm', CommandUsername=Username)        

    def AlarmDisarm(self, Username):
        try:
            os.remove(djangoproject.settings.AlarmDisarmPath + str(self.IPAddress))
        except:
            pass
        if(os.path.exists(djangoproject.settings.AlarmDisarmPath + str(self.IPAddress))):
            return 
        print 'wget --user=' + djangoproject.settings.AlarmUsername + ' --password=' + djangoproject.settings.AlarmPassword + \
               ' http://' + str(self.IPAddress) + r'/2?A=4\&p=1\&X=7103 -O ' + djangoproject.settings.AlarmDisarmPath + str(self.IPAddress)

        os.system('wget --user=' + djangoproject.settings.AlarmUsername + ' --password=' + djangoproject.settings.AlarmPassword + \
                   ' http://' + str(self.IPAddress) + r'/2?A=4\&p=1\&X=7103 -O ' + djangoproject.settings.AlarmDisarmPath + str(self.IPAddress))
        AlarmHistory.NewAlarmHistory(NetworkDevice=self, AlarmStatusFileName=djangoproject.settings.AlarmDisarmPath + str(self.IPAddress), 
                                     CommandDescription='Disarm', CommandUsername=Username)        


    def AlarmGetStatus(self):
        if(not self.NetworkDeviceModel.Name == 'DSC 1832 Alarm'):
            return
        try:
            os.remove(djangoproject.settings.AlarmGetStatusPath + str(self.IPAddress))
        except:
            pass
        if(os.path.exists(djangoproject.settings.AlarmGetStatusPath + str(self.IPAddress))):
            return 

        #print ('wget --timeout=10 --tries=2 --user=' + djangoproject.settings.AlarmUsername + ' --password=' + djangoproject.settings.AlarmPassword + \
        #           ' http://' + str(self.IPAddress) + ' -O ' + djangoproject.settings.AlarmGetStatusPath + str(self.IPAddress) + ' > /dev/null')
        os.system('wget --timeout=10 --tries=2 --user=' + djangoproject.settings.AlarmUsername + ' --password=' + djangoproject.settings.AlarmPassword + \
                   ' http://' + str(self.IPAddress) + ' -O ' + djangoproject.settings.AlarmGetStatusPath + str(self.IPAddress) + ' > /dev/null')
        AlarmHistory.NewAlarmHistory(NetworkDevice=self, AlarmStatusFileName=djangoproject.settings.AlarmGetStatusPath + str(self.IPAddress) ) 
        AlarmEventHistory.CreateAlarmEventHistoryIfNecessaryAfterReadingAlarm(self)
    def GetAlarmCurrentEventDescription(self):
        AlarmEventHistories=AlarmEventHistory.objects.filter(NetworkDevice=self).order_by('-StartDateTime')
        Results=[]
        if(AlarmEventHistories.count()):
            i = AlarmEventHistories[0]

            if(i.CloseDateTime):
                Results.append('Closed Event:  ' + str(i.CloseDateTime))
                Results.append(str(i.CloseUsername))
                Results.append(str(i.Description))
            elif(i.AcknowledgeDateTime):
                Results.append(str('Acknowledged Event:  ' + str(i.AcknowledgeDateTime)))
                Results.append(str(i.AcknowledgeUsername))
                Results.append(str(i.Description))
            else:
                Results.append('CURRENT EVENT IN PROGRESS:' + str(i))
        else:
            Results.append('NO EVENT HISTORY')
        return ''.join(Results)
    def GetAlarmEventOpenIndicator(self):
        Count=AlarmEventHistory.objects.filter(NetworkDevice=self).filter(CloseDateTime__isnull=True).count()
        return bool(Count)
    def GetOpenAlarmEventHistory(self):
        Count=AlarmEventHistory.objects.filter(NetworkDevice=self).filter(CloseDateTime__isnull=True).count()
        if(not Count):
            return None
        return AlarmEventHistory.objects.filter(NetworkDevice=self).filter(CloseDateTime__isnull=True)[0]
       





class NetworkDevicePort(models.Model):
    using='default'
    NetworkDevice=models.ForeignKey(NetworkDevice, db_column='networkdevice')
    Name=models.TextField(db_column='name', null=True, blank=True)  #Name/Number of Port (1,2,3,4,Internal,WAN1,WAN2, DMZ, etc)
    Description=models.TextField(db_column='description', null=True, blank=True)
    MACAddress=models.TextField(db_column='macaddress', null=True, blank=True)
    IPAddress=models.TextField(db_column='ipaddress', null=True, blank=True)  #Current/Primary IP Address for a port
    objects=models.GeoManager()
    class Meta:
        db_table=u'networkdeviceport'
        app_label='djangoproject'

class IPAddressAssignment(models.Model):
    using='default'
    IPAddress=models.TextField(db_column='ipaddress', null=True, blank=True)
    NetworkDevicePort=models.ForeignKey(NetworkDevicePort, db_column='networkdevice')
    StartDateTime=models.DateTimeField(db_column='startdatetime')
    EndDateTime=models.DateTimeField(db_column='enddatetime')
    class Meta:
        db_table=u'ipaddressassignment'
        app_label='djangoproject'


class NetworkDevicePortConnection(models.Model):
    using='default'
    NetworkDevicePort1=models.ForeignKey(NetworkDevicePort, db_column='networkdevice1', related_name='NetworkDevicePort1')
    NetworkDevicePort2=models.ForeignKey(NetworkDevicePort, db_column='networkdevice2', related_name='NetworkDevicePort2')
    Description=models.TextField(db_column='description', null=True, blank=True)
    class Meta:
        db_table=u'networkdeviceportconnection'
        app_label='djangoproject'



class NetworkDeviceTest(models.Model):
    using='default'
    NetworkDevice=models.ForeignKey(NetworkDevice, db_column='networkdevice')
    DateTime=models.DateTimeField(db_column='datetime')
    PingTime=models.FloatField(db_column='pingtime', null=True, blank=True) #Ping Time in ms
    ResponseIndicator=models.NullBooleanField(db_column='responseindicator', null=True, blank=True)
    BaseStationIdentifier=models.TextField(db_column='basestationidentifier', null=True, blank=True)
    DownlinkFrequency=models.FloatField(db_column='downlinkfrequency', null=True, blank=True) #frequency in MHz
    DownlinkChannelBandwidth=models.FloatField(db_column='downlinkchannelbandwidth', null=True, blank=True) #MHz
    SignalToNoiseRatio=models.FloatField(db_column='signaltonoiseratio', null=True, blank=True) #dB
    EffectiveSignalToNoiseRatio=models.FloatField(db_column='effectivesignaltonoiseratio', null=True, blank=True) #dB
    ReceivedSignalStrength=models.FloatField(db_column='receivedSignalStrength', null=True, blank=True) #dBm
    DownlinkModulation=models.TextField(db_column='downlinkmodulation', null=True, blank=True)
    UplinkFrequency=models.FloatField(db_column='uplinkfrequency', null=True, blank=True) #MHz
    UplinkChannelBandwidth=models.FloatField(db_column='uplinkchannelbandwidth', null=True, blank=True) #MHz
    TransmitPower=models.FloatField(db_column='transmitpower', null=True, blank=True) #dBm
    UplinkModulation=models.TextField(db_column='uplinkmodulation', null=True, blank=True)
    #applies only to EM01b Websensor
    Voltage=models.FloatField(db_column='voltage', null=True, blank=True)#DC volts
    Temperature=models.FloatField(db_column='temperature', null=True, blank=True)#degrees Fahrenheit
    RelativeHumidity=models.FloatField(db_column='relativehumidity', null=True, blank=True)#percentage
    objects=models.GeoManager()
    class Meta:
        db_table=u'networkdevicetest'
        app_label='djangoproject'


def GetDateTimeFromDictionary(Dict, FieldName):
    try:
        RawString=Dict[FieldName]
        ReturnObject=datetime.datetime.strptime(RawString,'%Y%m%d_%H%M')
    except:
        return None
    return ReturnObject


class Drive(models.Model):
    using='default'
    Name=models.TextField(db_column='name')
    Description=models.TextField(db_column='description', null=True, blank=True)
    Server=models.ForeignKey('Server',db_column='server')
    class Meta:
        db_table=u'drive'
        app_label='djangoproject'
    def CreateLog(self, ServerStatusLog, Dict):
        log=djangoproject.models.networkdevice.DriveLog()
        log.Drive=self
        log.ServerStatusLog=ServerStatusLog
        TotalSpace=float(Dict['Drive' + self.Name + 'TotalSpace'])
        FreeSpace=float(Dict['Drive' + self.Name + 'FreeSpace'])
        log.TotalSpace=TotalSpace
        log.FreeSpace=FreeSpace
        log.save()

class DriveLog(models.Model):
    using='default'
    Drive=models.ForeignKey('Drive', db_column='drive')
    TotalSpace=models.FloatField(db_column='totalspace')
    FreeSpace=models.FloatField(db_column='freespace')
    ServerStatusLog=models.ForeignKey('ServerStatusLog', db_column='serverstatuslog_id', null=True, blank=True)
    class Meta:
        db_table=u'drivelog'
        app_label='djangoproject'

class Server(models.Model):
    using='default'
    Name=models.TextField(db_column='name')
    Description=models.TextField(db_column='description', null=True, blank=True)
    IPAddress=models.TextField(db_column='ipaddress')
    class Meta:
        db_table=u'server'
        app_label='djangoproject'
    def __unicode__(self):
        return i.Name
    def CreateLog(self, Dict):
        import datetime
        log=djangoproject.models.networkdevice.ServerStatusLog()
        log.Server=self
        log.CreateDateTime=datetime.datetime.now()
        if 'BackupRemoteSize' in Dict:
            log.BackupRemoteSize=float(Dict['BackupRemoteSize'])
            log.BackupRemoteDateTime=GetDateTimeFromDictionary(Dict, 'BackupRemoteDateTime')
            log.BackupLocalSize=float(Dict['BackupLocalSize'])
            log.BackupLocalDateTime=GetDateTimeFromDictionary(Dict, 'BackupLocalDateTime')
        if 'TotalMemory' in Dict:
            log.TotalMemory=float(Dict['TotalMemory'])
            log.FreeMemory=float(Dict['FreeMemory'])
            log.TotalSwap=float(Dict['TotalSwap'])
            log.FreeSwap=float(Dict['FreeSwap'])        
        log.save()
        for key in Dict:
            if not 'TotalSpace' in key:
                continue
            CleanKey=key.replace('Drive', '').replace('TotalSpace', '')
            drive=Drive.objects.filter(Server=self).filter(Name=CleanKey)
            if drive.count():
                drive[0].CreateLog(log,Dict)
            else:
                log.ProblemIndicator=True
                log.save()


class ServerStatusLog(models.Model):
    using='default'
    Server=models.ForeignKey('Server', db_column='server')
    CreateDateTime=models.DateTimeField(db_column='createdatetime')
    BackupRemoteSize=models.FloatField(db_column='backupremotesize', null=True, blank=True)
    BackupRemoteDateTime=models.DateTimeField(db_column='backupremotedate', null=True, blank=True)
    BackupLocalSize=models.FloatField(db_column='backuplocalsize', null=True, blank=True)
    BackupLocalDateTime=models.DateTimeField(db_column='backuplocaldate', null=True, blank=True)
    ProblemIndicator=models.NullBooleanField(db_column='problemindicator', null=True, blank=True)
    TotalMemory=models.FloatField(db_column='totalmemory', null=True, blank=True)
    FreeMemory=models.FloatField(db_column='freememory', null=True, blank=True)
    TotalSwap=models.FloatField(db_column='totalswap', null=True, blank=True)
    FreeSwap=models.FloatField(db_column='freeswap', null=True, blank=True)
    class Meta:
        db_table=u'serverstatuslog'
        app_label='djangoproject'



class AlarmEventHistory(models.Model):
    using='default'
    NetworkDevice=models.ForeignKey(NetworkDevice, db_column='networkdevice')
    StartDateTime=models.DateTimeField(db_column='startdatetime')
    StartZoneStatus=models.TextField(db_column='startzonestatus') #pickled dict['zoneid']=Status, Closed is assumed if not in dict
    AcknowledgeDateTime=models.DateTimeField(db_column='acknowledgedatetime', null=True, blank=True)
    AcknowledgeUsername=models.TextField(db_column='acknowledgeusername', null=True, blank=True)
    PendingAcknowledgeNotification=models.NullBooleanField(db_column='pendingacknowledgenotification', null=True, blank=True)
    DisarmDateTime=models.DateTimeField(db_column='disarmdatetime', null=True, blank=True)
    DisarmUsername=models.TextField(db_column='disarmusername', null=True, blank=True)
    CloseDateTime=models.DateTimeField(db_column='closedatetime', null=True, blank=True)
    CloseUsername=models.TextField(db_column='closeusername', null=True, blank=True)
    Description=models.TextField(db_column='description', null=True, blank=True)
    objects=models.GeoManager()
    class Meta:
        db_table=u'alarmeventhistory'
        app_label='djangoproject'
    def GetStartZoneStatusDict(self):
        try:
            return cPickle.loads(str(self.StartZoneStatus))
        except cPickle.UnpicklingError:
            return {}
    def SetStartZoneStatusDict(self, StartZoneStatusDictString):
        self.StartZoneStatus = cPickle.dumps(StartZoneStatusDictString)
    @staticmethod 
    def CreateAlarmEventHistoryIfNecessaryAfterReadingAlarm(networkDevice):
        #1st see if there is an open AlarmEventHistory for this alarm, if so, then no need to create another one:
        if(AlarmEventHistory.objects.filter(NetworkDevice=networkDevice).filter(CloseDateTime__isnull=True)):
            return    
        #Now see if the alarm is in 'In Alarm' status
        alarmHistory = AlarmHistory.objects.filter(NetworkDevice=networkDevice).order_by('-CreateDateTime')
        if(alarmHistory.count()): 
            if(not alarmHistory[0].AlarmStatus.Name == 'In Alarm'):
                return  #Alarm is not current "In Alarm"
        else: 
            return #No Alarm History Records
        #alarmHistory[0] is the current state of the alarm 
        #Now make sure that a notification has not already been triggered from the alarms current state, such as if there is a loss of network connectivity
        alarmEventHistories=AlarmEventHistory.objects.filter(NetworkDevice=networkDevice).order_by('-StartDateTime')

        if(alarmEventHistories.count()):
            if(alarmHistory[0].CreateDateTime < alarmEventHistories[0].StartDateTime):
                return #An Alarm Event has already been created for this in alarm event

        alarmEventHistory=AlarmEventHistory()
        alarmEventHistory.NetworkDevice=networkDevice
        alarmEventHistory.StartDateTime=datetime.datetime.now()
        alarmEventHistory.StartZoneStatus=alarmHistory[0].ZoneStatus
        alarmEventHistory.save()

    @staticmethod 
    def SendNotifications():
        #Send Pending Acknowledgement Notifications
        AlarmEventHistories=AlarmEventHistory.objects.filter(PendingAcknowledgeNotification=True).order_by('StartDateTime')
        for i in AlarmEventHistories:
            i.SendAcknowledgementNotification()

        #Now Send New Event Notifications
        AlarmEventHistories=AlarmEventHistory.objects.filter(AcknowledgeDateTime__isnull=True).order_by('StartDateTime')
        for alarmEventHistory in AlarmEventHistories:
            NotificationLevel = alarmEventHistory.GetNotificationLevel()
            #print "Notification Level:  " + str(NotificationLevel)
            SecondsSinceLastNotification = alarmEventHistory.GetSecondsSinceLastNotification()
            #print "Seconds Since Last Notification:  " + str(SecondsSinceLastNotification)
            Group = AlarmContactGroup.objects.filter(PriorityLevel=NotificationLevel)
            print str(Group)
            if(Group):
                #print "Group Escalation Interval Seconds:  " + str(Group[0].EscalationIntervalSeconds)
                if(SecondsSinceLastNotification == -1) or (Group[0].EscalationIntervalSeconds < SecondsSinceLastNotification):
                    BodyList = []
                    BodyList.append('Level ' + str(NotificationLevel) + ' Notification:  \n')
                    Recipients = str(Group[0].Recipients).replace('\r', '').replace(' ', '').split('\n')

                    for i in Recipients:
                        BodyList.append(i + '\n')
                    BodyList.append('\n')
                    BodyList.append(str(alarmEventHistory))
                    Body = ''.join(BodyList)
                    Subject = 'Level ' + str(NotificationLevel) + ' Notification:  ' + str(alarmEventHistory.NetworkDevice) + ' Alarm has been Triggered:  ' + str(alarmEventHistory.StartDateTime)
                    AlarmEventNotification.SendNotification(alarmEventHistory, Recipients, NotificationLevel, Subject, Body)       
            else:
                pass #Do not send a notification

    def GetNotificationLevel(self):
        alarmEventNotifications=AlarmEventNotification.objects.filter(AlarmEventHistory=self)
        return alarmEventNotifications.count() + 1  #Notification Level increments by 1 for each notification sent out
    def GetSecondsSinceLastNotification(self):
        alarmEventNotifications=AlarmEventNotification.objects.filter(AlarmEventHistory=self).order_by('-DateTime')
        if(not alarmEventNotifications.count()):
            return -1
        else:
            return (datetime.datetime.now() - alarmEventNotifications[0].DateTime).total_seconds()
    def SendAcknowledgementNotification(self):
        NotificationLevel = self.GetNotificationLevel() - 1 #This level repesents levels that were notified of the alarm so far
        if(NotificationLevel < 1):
            return #No one was notified, so no need to send a notification email
        Recipients=[]
        for notificationLevel in range(1, NotificationLevel + 1):
            alarmContactGroup=AlarmContactGroup.objects.filter(PriorityLevel=notificationLevel)
            if(alarmContactGroup):
                TempRecipients = str(alarmContactGroup[0].Recipients).replace('\r', '').replace(' ', '').split('\n')
                for i in TempRecipients:
                    Recipients.append(i)
        BodyList = []
        BodyList.append('Alarm Acknowledgement:  \n')
        for i in Recipients:
            BodyList.append(str(i) + '\n')
        BodyList.append('\n')
        BodyList.append(str(self))
        Body = ''.join(BodyList)
        Subject = 'Acknowledgement:  ' + str(self.NetworkDevice) + ' Alarm has been Acknowledged:  ' + str(self.AcknowledgeDateTime) + ' by ' + self.AcknowledgeUsername
        AlarmEventNotification.SendNotification(self, Recipients, NotificationLevel, Subject, Body)
        self.PendingAcknowledgeNotification=False
        self.save()

    def __unicode__(self):
        StringList=[]
        if(self.AcknowledgeDateTime):
            StringList.append("ALARM ACKNOWLEDGED BY:  " + str(self.AcknowledgeUsername))
        StringList.append("\nEvent Start Date/Time:  " + str(self.StartDateTime) + '\n')
        StartZoneStatus = self.GetStartZoneStatusDict()
        StringList.append('\n' + str(self.NetworkDevice) + '\n' + '\n')
        StringList.append("Zone Status When Alarm Event was Created:  \n") 
        keys = StartZoneStatus.keys()
        keys.sort()
        for key in keys:
            StringList.append(key + ':  ' + str(StartZoneStatus[key]) + '\n')
        if(not keys):
            StringList.append('All Zones were closed.')
        if(self.Description):
            StringList.append(self.Description + '\n')
        return ''.join(StringList)

       
 
#table holds history of each notification sent per event
class AlarmEventNotification(models.Model):
    using='default'
    AlarmEventHistory=models.ForeignKey(AlarmEventHistory, db_column='alarmeventhistory', null=True, blank=True)
    AlarmEventDescription=models.TextField(db_column='alarmeventdescription', null=True, blank=True)  #Used for notifications that are not associated with an alarmeventhistrory object [notification of alarm changes from COOPWeb for example]
    NotificationLevel=models.IntegerField(db_column='notificationlevel')  #Starts at 1, increments for each notification sent on an alarm
    DateTime=models.DateTimeField(db_column='datetime')
    Recipients=models.TextField(db_column='recipients') 
    Body=models.TextField(db_column='body')
    Subject=models.TextField(db_column='subject')
    objects=models.GeoManager()
    class Meta:
        db_table=u'alarmeventnotification'
        app_label='djangoproject'
    def GetStartZoneStatusDict(self):
        try:
            return cPickle.loads(str(self.StartZoneStatus))
        except cPickle.UnpicklingError:
            return {}
    def SetStartZoneStatusDict(self, StartZoneStatusDictString):
        self.StartZoneStatus = cPickle.dumps(StartZoneStatusDictString)
    @staticmethod
    def SendNotification(alarmEventHistory, Recipients, NotificationLevel, Subject, Body, AlarmEventDescription=''):
        New=AlarmEventNotification()
        if(alarmEventHistory):
            New.AlarmEventHistory=alarmEventHistory
        New.AlarmEventDescription=AlarmEventDescription
        New.Recipients=str(Recipients)
        New.NotificationLevel=NotificationLevel
        New.Subject=Subject
        New.Body=Body
        New.DateTime=datetime.datetime.now()
        New.SendEmail()
        New.save()
    def SendEmail(self):
        msg=MIMEText(self.Body)
        Sender = 'AlarmNotification@tce.coop'
        msg['Subject'] = self.Subject
        msg['From'] = Sender
        To = self.Recipients.replace('[', '').replace(']', '').replace("'", '').split(',')
        msg['To'] = self.Recipients.replace('[', '').replace(']', '').replace("'", '').rstrip(',')
        Server = '192.168.100.8'
        SMTPSession = smtplib.SMTP(Server)
        SMTPSession.sendmail(Sender, To, msg.as_string())
        SMTPSession.quit()




class AlarmContactGroup(models.Model):
    PriorityLevel=models.IntegerField(db_column='prioritylevel')
    Recipients=models.TextField(db_column='recipients')
    EscalationIntervalSeconds=models.FloatField(db_column='escalationintervalseconds')
    class Meta:
        db_table=u'alarmcontactgroup'
        app_label='djangoproject'
    def GetRecipients(self):
        return str(self.Recipients).replace('\r', '').replace(' ', '').split('\n')


        
